#!/usr/bin/python
import mysql.connector

from tkinter import *
# 导入ttk
from tkinter import ttk
# 导入filedialog
from tkinter import filedialog
from tkinter.messagebox import *
from tkinter import *



# re_status 0未,1ok,9模糊,8常识
mydb = mysql.connector.connect(
    host="192.168.47.102",  # 数据库主机地址
    user="myuser",  # 数据库用户名
    port=3307,
    database="iridescent_study",  # 数据库名称 没有的话插入操作报错
    passwd="211314wccA@",  # 数据库密码
)

mycursor = mydb.cursor()

# 插入字典表
init = " SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN '模糊' WHEN re_status =2 THEN '熟记'  WHEN re_status =3 THEN '常用' END AS STATUS , COUNT(1) AS COUNT FROM egl_word GROUP BY re_status order by re_status "
init2 = "SELECT CASE WHEN re_status =0 THEN '未记' WHEN re_status =1 THEN '模糊' WHEN re_status =2 THEN '熟记'  WHEN re_status =3 THEN '常用' END AS STATUS , COUNT(1) AS COUNT FROM egl_word where group_id ={} GROUP BY re_status;"
sql = "select * from egl_word where re_status ={} and group_id ={} ORDER BY RAND() LIMIT 1"
sql2 = "select * from egl_word where re_status ={}  ORDER BY RAND() LIMIT 1"
delete_sql = "DELETE FROM egl_word WHERE id = {} "
update_sql = "UPDATE  egl_word SET re_status ={} WHERE id ={}"
group_msg = '''
SELECT t.group_id,IFNULL(a.num, 0) AS no_num,IFNULL(b.num , 0)AS mo_num ,IFNULL(c.num , 0)AS yes_num,IFNULL(d.num , 0)AS shu_num

FROM 
(SELECT DISTINCT group_id FROM egl_word ) t

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word 
WHERE re_status =0
GROUP BY re_status,group_id) a
ON t.group_id =a.group_id
LEFT JOIN 
(
SELECT 
group_id,
COUNT(1) AS num FROM egl_word 
WHERE re_status =1
GROUP BY re_status,group_id)b
ON t.group_id =b.group_id

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word 
WHERE re_status =2
GROUP BY re_status,group_id) c
ON t.group_id =c.group_id

LEFT JOIN 
(
SELECT 
 group_id,
COUNT(1) AS num FROM egl_word 
WHERE re_status =3
GROUP BY re_status,group_id) d
ON t.group_id =d.group_id

 ORDER BY group_id ASC
 '''


class App:
    def __init__(self, master):
        self.master = master
        self.is_open_type = False  # False表示当前未开启，True表示当前开启
        self.str = StringVar()
        self.str.set('0天 00：00：00')

        self.iv_aligning = IntVar()
        self.iv_aligning.set(-1)

        self.group_msg = StringVar()
        self.group_msg.set('分组信息')

        self.group_clase_msg = StringVar()
        self.group_clase_msg.set('全部类型信息')

        self.chose_msg = StringVar()
        self.chose_msg.set('分组分类信息')

        self.group_id = StringVar()
        self.group_id.set('1')

        self.chose_select_type = StringVar()
        self.chose_select_type.set('1')

        self.world_id = StringVar()
        self.world_id.set('1')

        self.world_msg = StringVar()
        self.world_msg.set('1')

        self.chi_msg = StringVar()
        self.chi_msg.set('1')

        self.group_pull_down=['0']
        self.get_group_msg()
        self.get_msg2()

        self.initWidgets()






    def initWidgets(self):
        print(self.str.get())
        self.lable_title = ttk.Label(self.master, textvariable=self.group_msg, background="white", font=("微软雅黑", 10))

        # self.group_id = Text(self.master, height=1, width=10, highlightcolor='red', highlightthickness=1)
        # self.chose_button = ttk.Button(self.master, text='确定分组', command=self.chose_group)

        # 设置默认值，即默认下拉框中的内容,索引从0开始
        self.ddb_default_L = Label(self.master, text='分组选择：')
        self.ddb_default = ttk.Combobox(self.master,textvariable=self.group_id)
        self.ddb_default['value'] = self.group_pull_down
        self.ddb_default.current(0)
        self.ddb_default.bind("<<ComboboxSelected>>", self.chose_group)

        self.button_text_label = Label(self.master, text='选择分组：')
        self.button_text = Text(self.master, height=3, width=20)
        self.button_text.insert('0.0', '1')
        self.text_button = Button(self.button_text, text='确定', command=self.chose_group)
        self.button_text.window_create(INSERT, window=self.text_button)

        self.button_text_label2 = Label(self.master, text='选择查询类型 : 0未,1模糊,2熟记,3常用：')
        self.button_text2 = Text(self.master, height=3, width=20)
        self.button_text2.insert('0.0', '0')
        self.text_button2 = Button(self.button_text2, text='确定', command=self.chose_type)
        self.button_text2.window_create(INSERT, window=self.text_button2)

        self.all_group_class_msg = ttk.Label(self.master, textvariable=self.group_clase_msg,  font=("微软雅黑", 10),foreground='blue',background='white')

        self.chose_title = ttk.Label(self.master, textvariable=self.chose_msg,  font=("微软雅黑", 10),foreground='blue',background='white')

        self.world_msg_label = ttk.Label(self.master, textvariable=self.world_msg, background="white", font=("微软雅黑", 10))
        self.chi_msg_label = ttk.Label(self.master, textvariable=self.chi_msg, background="white", font=("微软雅黑", 10))

        self.start_select_button = ttk.Button(self.master, text='开始查询', command=self.start_select)
        print(self.iv_aligning.get())
        # tmp=self.iv_aligning.get()
        self.rb_aligning_Label = Label(root, text='文字对齐方式：')
        self.rb_aligning1 = Radiobutton(root, text='未记', value=0,command=self.update_status,  variable=self.iv_aligning, justify=LEFT)
        self.rb_aligning2 = Radiobutton(root, text='模糊', value=1,command=self.update_status, variable=self.iv_aligning, justify=LEFT)
        self.rb_aligning3 = Radiobutton(root, text='熟记', value=2,command=self.update_status,  variable=self.iv_aligning, justify=LEFT)
        self.rb_aligning4 = Radiobutton(root, text='常用', value=3,command=self.update_status,  variable=self.iv_aligning, justify=LEFT)

        # self.lable_time = ttk.Label(self.master, textvariable=self.str, font=("微软雅黑", 28), foreground='blue',
        #                             background='red')
        # self.button_sync = ttk.Button(self.master, text='开启同步', command=self.sync_file)


        self.lable_title.grid(row=1, column=0, columnspan=12, pady=10)
        # self.group_id.grid(row=2, column=1, columnspan=2, pady=10)
        # self.chose_button.grid(row=2, column=2, columnspan=2, pady=10)
        self.ddb_default_L.grid(row=2, column=0, sticky='W')
        self.ddb_default.grid(row=2, column=1, sticky='NW')
        self.chose_title.grid(row=2, column=2, sticky='NW')


        self.all_group_class_msg.grid(row=29, column=2, columnspan=2, pady=10)
        #
        #
        # self.button_text_label.grid(row=29, column=0,columnspan=2)
        # self.button_text.grid(row=29, column=1, columnspan=2)


        self.button_text_label2.grid(row=3, column=0,columnspan=2)
        self.button_text2.grid(row=3, column=1, columnspan=2)

        self.start_select_button.grid(row=5, column=1, columnspan=2)

        self.world_msg_label.grid(row=6, column=0, columnspan=2)
        self.chi_msg_label.grid(row=6, column=1, columnspan=2)

        # self.rb_aligning_Label.grid(row=7, column=0, sticky='E')
        self.rb_aligning1.grid(row=7, column=1,columnspan=1, sticky='E')
        self.rb_aligning2.grid(row=8, column=1,columnspan=1, sticky='E')
        self.rb_aligning3.grid(row=9, column=1,columnspan=1, sticky='E')
        self.rb_aligning4.grid(row=10, column=1,columnspan=1, sticky='E')
        # self.rb_aligning3.grid(row=7, column=4, sticky='W')
        # self.rb_aligning3.grid(row=7, column=5, sticky='W')
        # self.rb_aligning3.grid(row=7, column=6, sticky='W')

        # self.lable_time.grid(row=5, column=1, columnspan=2)
        # self.button_sync.grid(row=6, column=1, columnspan=2, pady=10)


    def sync_file(self):

        if self.is_open_type == False:
            self.button_sync['text'] = '关闭'
            self.str.set('修改的内容为关闭')
            self.is_open_type = True
        else:
            self.button_sync['text'] = '开启同步'
            self.str.set('修改的内容为开启')
            self.is_open_type = False
        print(self.lable_title['text'])

    def start_select(self):
        try:
            word_data = {};
            # print(self.chose_select_type.get())
            # print(self.group_id.get().__str__())
            # tmpStr =self.group_id.get()
            # print(type(tmpStr))
            # print(type('0'))
            # print(tmpStr is '0')

            # if self.group_id.get().__str__().__eq__(tmpStr) :
            #     print("查询全部")
            #     word_data = self.get_date2(self.chose_select_type.get())
            # else:
            #     print("查询分组")
            word_data = self.get_date(self.chose_select_type.get(), self.group_id.get())
            self.world_id.set(word_data[0])
            self.world_msg.set(word_data[1])
            self.chi_msg.set(word_data[2])
        except:
            print("查询出异常了")

    def chose_group(self,group_id):
        #print(self.group_id.get())
        # print('当前分组:'+group_id)
        self.get_msg(self.group_id.get())

       # print('当前分组:'.__add__(id.__str__()).__str__())

    def chose_type(self):
        self.chose_select_type.set(self.button_text2.get('0.0', 'end').__str__())
        #print(self.group_id.get())
        print('查询类型:'.__add__(self.button_text2.get('0.0', 'end').__str__()).__str__())
        # if self.button_text2.get('1.1', 'end'):
        #     self.get_msg(self.button_text2.get('1.1', 'end'))
        # else:
        #     self.get_msg2()

    def get_group_msg(self):
        mycursor.execute(group_msg)
        results = mycursor.fetchall()
        count = 0
        row_num=1
        stt=''
        for row in results:
            row_str = row[0].__str__()
            if len(row_str) != 2:
                row_str = "0" + row_str
            row1_str = row[1].__str__()
            while len(row1_str) != 3:
                row1_str = "0" + row1_str
            row2_str = row[2].__str__()
            while len(row2_str) != 3:
                row2_str = "0" + row2_str
            row3_str = row[3].__str__()
            while len(row3_str) != 3:
                row3_str = "0" + row3_str
            row4_str = row[4].__str__()
            while len(row4_str) != 3:
                row4_str = "0" + row4_str
            print_str = row_str + "组,未:" + row1_str + ",模:" + row2_str + ",熟:" + row3_str + ",常:" + row4_str + "|";
            if count == 6:
                stt=stt+print_str+"\n"
                print(print_str)
                count = 0
            stt = stt + print_str
            print(print_str, end="")
            count = count + 1;

            self.group_pull_down.append(row_num.__str__())

            # tt=(row_num.__str__())
            # tt1=('ga',12,33)
            # self.group_pull_down=self.group_pull_down+tt
            # print('----------------')
            # print(tt1)
            # print('----------------')
            # print(len(self.group_pull_down))
            # print(self.group_pull_down)
            # print('----------------')
            row_num=row_num+1
        print()
        #print(stt)
        self.group_msg.set(stt)
    #删除单词
    def delete_word(self,id):
        tmp_sql = delete_sql.format(id)
        mycursor.execute(tmp_sql)
        mydb.commit()
    #通过分组id获取分类信息
    def get_msg(self,group_id):
        tmp_sql = init2.format(group_id)
        mycursor.execute(tmp_sql)
        results = mycursor.fetchall()
        results_str = "";
        for row in results:
            results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
        self.chose_msg.set(results_str)
        print(results_str)
    #获取全部分类信息
    def get_msg2(self):
        mycursor.execute(init)
        results = mycursor.fetchall()
        results_str = "";
        for row in results:
            results_str = results_str + row[0] + ":" + row[1].__str__() + "个, "
        self.group_clase_msg.set(results_str)
        print(results_str)

    def get_date(self,data_type, group_id):
        sss="----------"+group_id+"-----------"
        print(sss)
        if group_id.__eq__('0+\t'):
            print(group_id)
            print("0组")
        else:
            print(group_id)
            print("1111")
            print("222")
        tmp_sql = sql.format(data_type, group_id)
        mycursor.execute(tmp_sql)
        results = mycursor.fetchall()
        if results:
            return results[0]
        else:
            exit("no data")

    def get_date2(self,data_type):
        tmp_sql = sql2.format(data_type, group_id)
        mycursor.execute(tmp_sql)
        results = mycursor.fetchall()
        return results[0]

    def update_status(self):
        print(self.iv_aligning.get())
        print(self.world_id)
        tmp_sql = update_sql.format(self.iv_aligning.get(), self.world_id.get())
        # print(tmp_sql)
        mycursor.execute(tmp_sql)
        mydb.commit()
        self.get_group_msg()
        self.chose_group()
        self.iv_aligning.set(-1)

    def group_word(self,size):
        print("分组size为: " + size.__str__())
        for i in range(int(6000 / int(size))):
            # if i == 0:
            #     #continue
            start_num = i * int(size)
            # print(start_num)
            group_sql = "UPDATE  egl_word SET group_id =" + (
                        i + 1).__str__() + " WHERE id in ( select id from ( SELECT id from egl_word order by id asc limit " + start_num.__str__() + "," + size + ") a )"
            # print(group_sql)
            mycursor.execute(group_sql)
            mydb.commit()


root = Tk()
root.title('单词本')
root.geometry("1600x600")
App(root)


root.mainloop()
